package basedatos;

import genericos.Conexion;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author aux_consultoria
 */
public class SCorreo {
    //static Connection conn = null;
    //static String url = "jdbc:mysql://localhost:3306/";
    //static String dbName = "zianya";
    //static String driver = "com.mysql.jdbc.Driver";
    //static String[] userpass = Conexion.userpass();
    static Pool p=new Pool();
    /**
     * Este método busca el correo del director general
     * @return correo del director general
     */
    public static String Ccolaborador(int cedula) {
        String jefeIn = "";
        String sql = "select correo from usuarios where id= (select usuario from cedula where id=?)";
        PreparedStatement pst = null;
        Connection conn = null;
        try {
            conn = p.extraerConexion();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, cedula);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                jefeIn = rs.getString(1);
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        } finally {
            try {
                pst.close();
                conn.close();
                ////System.gc();
            } catch (SQLException ex) {
                System.out.println(ex);
            }
        }
        System.out.println("El correo del colaborador es: "+jefeIn);
        return jefeIn;
    }
     

public static String CDirector(int id) {
        String director = "";
        String sql = "select correo from usuarios where id= (select usuario from cedula where id=(select c.id from usuarios a,dependencia b,cedula c where a.id=c.usuario and a.nivel= 2 and a.puesto=b.idpuesto and b.dep=?));";
        PreparedStatement pst = null;
        Connection conn = null;
        try {
            conn = p.extraerConexion();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                director = rs.getString(1);
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        } finally {
            try {
                pst.close();
                conn.close();
                ////System.gc();
            } catch (SQLException ex) {
                System.out.println(ex);
            }
        }
        System.out.println("El correo del director de área es: "+director);
        return director;
    }

    public static String CDirGen() {
        String dirGen = "";
        String sql = "select correo from usuarios where puesto=(select id from puestos where nombre='Dirección General');";
        PreparedStatement pst = null;
        Connection conn = null;
        try {
            conn = p.extraerConexion();
            pst = conn.prepareStatement(sql);
            //pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                dirGen = rs.getString(1);
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        } finally {
            try {
                pst.close();
                conn.close();
                //System.gc();
            } catch (SQLException ex) {
                System.out.println(ex);
            }
        }
        System.out.println("El correo del director general es: "+dirGen);
        return dirGen;
    }
    
     public static String CDirTurno() {
        String dirTurno = "";
        String sql = "select correo from usuarios a,direccion b where a.nivel= 2 and b.turno=1;";
        PreparedStatement pst = null;
        Connection conn = null;
        try {
            conn = p.extraerConexion();
            pst = conn.prepareStatement(sql);
            //pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                dirTurno = rs.getString(1);
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        } finally {
            try {
                pst.close();
                conn.close();
                //System.gc();
            } catch (SQLException ex) {
                System.out.println(ex);
            }
        }
        System.out.println("El correo del director en turno es: "+dirTurno);
        return dirTurno;
    }
     
     public static String CjefeIn(int id) {
        String jefeIn = "";
        String sql = "select correo from usuarios where puesto=(select arriba from detpuesto where idpuesto=?);";
        PreparedStatement pst = null;
        Connection conn = null;
        try {
            conn = p.extraerConexion();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                jefeIn = rs.getString(1);
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        } finally {
            try {
                pst.close();
                conn.close();
                ////System.gc();
            } catch (SQLException ex) {
                System.out.println(ex);
            }
        }
        System.out.println("El correo de tu jefe inmediato es: "+jefeIn);
        return jefeIn;
    }
     
     public static String CdirArea(int id) {
        String dirArea = "";
        String sql = "select correo from usuarios a, direccion b where a.nivel=2 and b.id=(select dep from dependencia where idpuesto=?);";
        PreparedStatement pst = null;
        Connection conn = null;
        try {
            conn = p.extraerConexion();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                dirArea = rs.getString(1);
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        } finally {
            try {
                pst.close();
                conn.close();
                ////System.gc();
            } catch (SQLException ex) {
                System.out.println(ex);
            }
        }
        System.out.println("El correo del director de área es: "+dirArea);
        return dirArea;
    }
     
}
    